package com.gitee.qdbp.jdbc.test.service;

import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.gitee.qdbp.able.jdbc.condition.DbWhere;
import com.gitee.qdbp.able.jdbc.condition.TableJoin;
import com.gitee.qdbp.able.jdbc.ordering.OrderPaging;
import com.gitee.qdbp.able.jdbc.ordering.Orderings;
import com.gitee.qdbp.able.jdbc.paging.PartList;
import com.gitee.qdbp.jdbc.api.CrudDao;
import com.gitee.qdbp.jdbc.api.QdbcBoot;
import com.gitee.qdbp.jdbc.sql.fragment.QueryFragmentHelper;
import com.gitee.qdbp.jdbc.test.enums.Gender;
import com.gitee.qdbp.jdbc.test.enums.UserSource;
import com.gitee.qdbp.jdbc.test.enums.UserState;
import com.gitee.qdbp.jdbc.test.enums.UserType;
import com.gitee.qdbp.jdbc.test.model.RoleIdUserResult;
import com.gitee.qdbp.jdbc.test.model.SysDeptEntity;
import com.gitee.qdbp.jdbc.test.model.SysRoleEntity;
import com.gitee.qdbp.jdbc.test.model.SysUserEntity;
import com.gitee.qdbp.jdbc.test.model.SysUserRoleEntity;
import com.gitee.qdbp.jdbc.test.model.UserAndDeptResult;
import com.gitee.qdbp.jdbc.test.model.UserIdRoleResult;
import com.gitee.qdbp.tools.utils.StringTools;
import com.gitee.qdbp.tools.utils.VerifyTools;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class SysUserStream {

    @Autowired
    private QdbcBoot qdbcBoot;

    /** 查询用户信息 (来自sql模板) **/
    public PartList<UserAndDeptResult> getUserInfosForSql(Map<String, Object> where, OrderPaging odpg) {
        String sqlId = "user.info.query";
        return doQueryUserInfos(sqlId, where, odpg);
    }

    /** 查询用户信息 (来自xml模板) **/
    public PartList<UserAndDeptResult> getUserInfosForXml(Map<String, Object> where, OrderPaging odpg) {
        String sqlId = "SysUserMapper:queryUserInfos";
        return doQueryUserInfos(sqlId, where, odpg);
    }

    private PartList<UserAndDeptResult> doQueryUserInfos(String sqlId, Map<String, Object> where, OrderPaging odpg) {
        Map<String, Object> params = new HashMap<>();
        params.put("where", where);
        params.put("orderings", odpg.getOrderings());
        // sqlDao.pageForObjects(sqlId, params, odpg, UserAndDeptResult.class);
        // @formatter:off
        return qdbcBoot.sqlStream()
                .sqlId(sqlId)
                .params(params)
                .pageBy(odpg)
                .resultAs(UserAndDeptResult.class)
                .list().asPartList();
        // @formatter:on
    }

    /** 查询指定用户所分配的角色信息 **/
    public List<SysRoleEntity> getUserRolesForSql(String userId, DbWhere where, Orderings orderings) {
        String sqlId = "user.roles.query";
        return doQueryUserRoles(sqlId, userId, where, orderings);
    }

    /** 查询指定用户所分配的角色信息 **/
    public List<SysRoleEntity> getUserRolesForXml(String userId, DbWhere where, Orderings orderings) {
        String sqlId = "SysUserMapper:queryUserRoles";
        return doQueryUserRoles(sqlId, userId, where, orderings);
    }

    private List<SysRoleEntity> doQueryUserRoles(String sqlId, String userId, DbWhere where, Orderings orderings) {
        VerifyTools.requireNonNull(userId, "userId");

        TableJoin tables = TableJoin.of(SysUserRoleEntity.class, "ur", SysRoleEntity.class, "r");
        QueryFragmentHelper sqlHelper = qdbcBoot.sqlHelper(tables).fragment();

        Map<String, Object> params = new HashMap<>();
        params.put("selectColumns", sqlHelper.buildSelectFieldsSql("r.*"));
        params.put("userIds", Collections.singletonList(userId));
        if (VerifyTools.isNotBlank(where)) {
            params.put("whereCondition", sqlHelper.buildWhereSql(where, false));
        }
        if (VerifyTools.isNotBlank(orderings)) {
            params.put("orderByCondition", sqlHelper.buildOrderBySql(orderings, false));
        }

        // return sqlDao.listForObjects(sqlId, params, SysRoleEntity.class);
        // @formatter:off
        return qdbcBoot.sqlStream()
                .sqlId(sqlId)
                .params(params)
                .resultAs(SysRoleEntity.class)
                .list();
        // @formatter:on
    }

    /** 批量查询用户所分配的角色信息 **/
    public List<UserIdRoleResult> getUserRolesForSql(List<String> userIds, DbWhere where, Orderings orderings) {
        String sqlId = "user.roles.query";
        return doQueryUserRoles(sqlId, userIds, where, orderings);
    }

    /** 批量查询用户所分配的角色信息 **/
    public List<UserIdRoleResult> getUserRolesForXml(List<String> userIds, DbWhere where, Orderings orderings) {
        String sqlId = "SysUserMapper:queryUserRoles";
        return doQueryUserRoles(sqlId, userIds, where, orderings);
    }

    private List<UserIdRoleResult> doQueryUserRoles(String sqlId, List<String> userIds, DbWhere where,
            Orderings orderings) {
        VerifyTools.requireNonNull(userIds, "userIds");

        TableJoin tables = TableJoin.of(SysUserRoleEntity.class, "ur", SysRoleEntity.class, "r");
        QueryFragmentHelper sqlHelper = qdbcBoot.sqlHelper(tables).fragment();

        Map<String, Object> params = new HashMap<>();
        params.put("selectColumns", sqlHelper.buildSelectFieldsSql("ur.userId,r.*"));
        params.put("userIds", userIds);
        if (VerifyTools.isNotBlank(where)) {
            params.put("whereCondition", sqlHelper.buildWhereSql(where, false));
        }
        if (VerifyTools.isNotBlank(orderings)) {
            params.put("orderByCondition", sqlHelper.buildOrderBySql(orderings, false));
        }

        // return sqlDao.listForObjects(sqlId, params, UserIdRoleResult.class);
        // @formatter:off
        return qdbcBoot.sqlStream()
                .sqlId(sqlId)
                .params(params)
                .resultAs(UserIdRoleResult.class)
                .list();
        // @formatter:on
    }

    /** 查询指定用户所分配的角色信息 **/
    public PartList<SysRoleEntity> getUserRolesForSql(String userId, DbWhere where, OrderPaging odpg) {
        String sqlId = "user.roles.query";
        return doQueryUserRoles(sqlId, userId, where, odpg);
    }

    /** 查询指定用户所分配的角色信息 **/
    public PartList<SysRoleEntity> getUserRolesForXml(String userId, DbWhere where, OrderPaging odpg) {
        String sqlId = "SysUserMapper:queryUserRoles";
        return doQueryUserRoles(sqlId, userId, where, odpg);
    }

    private PartList<SysRoleEntity> doQueryUserRoles(String sqlId, String userId, DbWhere where, OrderPaging odpg) {
        VerifyTools.requireNonNull(userId, "userId");

        TableJoin tables = TableJoin.of(SysUserRoleEntity.class, "ur", SysRoleEntity.class, "r");
        QueryFragmentHelper sqlHelper = qdbcBoot.sqlHelper(tables).fragment();

        Map<String, Object> params = new HashMap<>();
        params.put("selectColumns", sqlHelper.buildSelectFieldsSql("r.*"));
        params.put("userIds", Collections.singletonList(userId));
        if (VerifyTools.isNotBlank(where)) {
            params.put("whereCondition", sqlHelper.buildWhereSql(where, false));
        }
        if (VerifyTools.isNotBlank(odpg.getOrderings())) {
            params.put("orderByCondition", sqlHelper.buildOrderBySql(odpg.getOrderings(), false));
        }

        // return sqlDao.pageForObjects(sqlId, params, odpg, SysRoleEntity.class);
        // @formatter:off
        return qdbcBoot.sqlStream()
                .sqlId(sqlId)
                .params(params)
                .pageBy(odpg)
                .resultAs(SysRoleEntity.class)
                .list().asPartList();
        // @formatter:on
    }

    /** 批量查询用户所分配的角色信息 **/
    public PartList<UserIdRoleResult> getUserRolesForSql(List<String> userIds, DbWhere where, OrderPaging odpg) {
        String sqlId = "user.roles.query";
        return doQueryUserRoles(sqlId, userIds, where, odpg);
    }

    /** 批量查询用户所分配的角色信息 **/
    public PartList<UserIdRoleResult> getUserRolesForXml(List<String> userIds, DbWhere where, OrderPaging odpg) {
        String sqlId = "SysUserMapper:queryUserRoles";
        return doQueryUserRoles(sqlId, userIds, where, odpg);
    }

    private PartList<UserIdRoleResult> doQueryUserRoles(String sqlId, List<String> userIds, DbWhere where,
            OrderPaging odpg) {
        VerifyTools.requireNonNull(userIds, "userIds");

        TableJoin tables = TableJoin.of(SysUserRoleEntity.class, "ur", SysRoleEntity.class, "r");
        QueryFragmentHelper sqlHelper = qdbcBoot.sqlHelper(tables).fragment();

        Map<String, Object> params = new HashMap<>();
        params.put("selectColumns", sqlHelper.buildSelectFieldsSql("ur.userId,r.*"));
        params.put("userIds", userIds);
        if (VerifyTools.isNotBlank(where)) {
            params.put("whereCondition", sqlHelper.buildWhereSql(where, false));
        }
        if (VerifyTools.isNotBlank(odpg.getOrderings())) {
            params.put("orderByCondition", sqlHelper.buildOrderBySql(odpg.getOrderings(), false));
        }

        // return sqlDao.pageForObjects(sqlId, params, odpg, UserIdRoleResult.class);
        // @formatter:off
        return qdbcBoot.sqlStream()
                .sqlId(sqlId)
                .params(params)
                .pageBy(odpg)
                .resultAs(UserIdRoleResult.class)
                .list().asPartList();
        // @formatter:on
    }

    /** 查询指定角色下的用户信息 (来自sql模板) **/
    public List<SysUserEntity> getRoleUsersForSql(String roleId, DbWhere where, Orderings orderings) {
        String sqlId = "role.users.query";
        return doQueryRoleUsers(sqlId, roleId, where, orderings);
    }

    /** 查询指定角色下的用户信息 (来自xml模板) **/
    public List<SysUserEntity> getRoleUsersForXml(String roleId, DbWhere where, Orderings orderings) {
        String sqlId = "SysUserMapper:queryRoleUsers";
        return doQueryRoleUsers(sqlId, roleId, where, orderings);
    }

    private List<SysUserEntity> doQueryRoleUsers(String sqlId, String roleId, DbWhere where, Orderings orderings) {
        VerifyTools.requireNonNull(roleId, "roleId");

        TableJoin tables = TableJoin.of(SysUserRoleEntity.class, "ur", SysUserEntity.class, "u");
        QueryFragmentHelper sqlHelper = qdbcBoot.sqlHelper(tables).fragment();

        Map<String, Object> params = new HashMap<>();
        params.put("selectColumns", sqlHelper.buildSelectFieldsSql("u.*"));
        params.put("roleIds", Collections.singletonList(roleId));
        if (VerifyTools.isNotBlank(where)) {
            params.put("whereCondition", sqlHelper.buildWhereSql(where, false));
        }
        if (VerifyTools.isNotBlank(orderings)) {
            params.put("orderByCondition", sqlHelper.buildOrderBySql(orderings, false));
        }

        // return sqlDao.listForObjects(sqlId, params, SysUserEntity.class);
        // @formatter:off
        return qdbcBoot.sqlStream()
                .sqlId(sqlId)
                .params(params)
                .resultAs(SysUserEntity.class)
                .list();
        // @formatter:on
    }

    /** 批量查询指定角色下的用户信息 (来自sql模板) **/
    public List<RoleIdUserResult> getRoleUsersForSql(List<String> roleIds, DbWhere where, Orderings orderings) {
        String sqlId = "role.users.query";
        return doQueryRoleUsers(sqlId, roleIds, where, orderings);
    }

    /** 批量查询指定角色下的用户信息 (来自xml模板) **/
    public List<RoleIdUserResult> getRoleUsersForXml(List<String> roleIds, DbWhere where, Orderings orderings) {
        String sqlId = "SysUserMapper:queryRoleUsers";
        return doQueryRoleUsers(sqlId, roleIds, where, orderings);
    }

    private List<RoleIdUserResult> doQueryRoleUsers(String sqlId, List<String> roleIds, DbWhere where,
            Orderings orderings) {
        VerifyTools.requireNonNull(roleIds, "roleIds");

        TableJoin tables = TableJoin.of(SysUserRoleEntity.class, "ur", SysUserEntity.class, "u");
        QueryFragmentHelper sqlHelper = qdbcBoot.sqlHelper(tables).fragment();

        Map<String, Object> params = new HashMap<>();
        params.put("selectColumns", sqlHelper.buildSelectFieldsSql("ur.roleId,u.*"));
        params.put("roleIds", roleIds);
        if (VerifyTools.isNotBlank(where)) {
            params.put("whereCondition", sqlHelper.buildWhereSql(where, false));
        }
        if (VerifyTools.isNotBlank(orderings)) {
            params.put("orderByCondition", sqlHelper.buildOrderBySql(orderings, false));
        }

        // return sqlDao.listForObjects(sqlId, params, .class);
        // @formatter:off
        return qdbcBoot.sqlStream()
                .sqlId(sqlId)
                .params(params)
                .resultAs(RoleIdUserResult.class)
                .list();
        // @formatter:on
    }

    /** 查询指定角色下的用户信息 (来自sql模板) **/
    public PartList<SysUserEntity> getRoleUsersForSql(String roleId, DbWhere where, OrderPaging odpg) {
        String sqlId = "role.users.query";
        return doQueryRoleUsers(sqlId, roleId, where, odpg);
    }

    /** 查询指定角色下的用户信息 (来自xml模板) **/
    public PartList<SysUserEntity> getRoleUsersForXml(String roleId, DbWhere where, OrderPaging odpg) {
        String sqlId = "SysUserMapper:queryRoleUsers";
        return doQueryRoleUsers(sqlId, roleId, where, odpg);
    }

    private PartList<SysUserEntity> doQueryRoleUsers(String sqlId, String roleId, DbWhere where, OrderPaging odpg) {
        VerifyTools.requireNonNull(roleId, "roleId");

        TableJoin tables = TableJoin.of(SysUserRoleEntity.class, "ur", SysUserEntity.class, "u");
        QueryFragmentHelper sqlHelper = qdbcBoot.sqlHelper(tables).fragment();

        Map<String, Object> params = new HashMap<>();
        params.put("selectColumns", sqlHelper.buildSelectFieldsSql("u.*"));
        params.put("roleIds", Collections.singletonList(roleId));
        if (VerifyTools.isNotBlank(where)) {
            params.put("whereCondition", sqlHelper.buildWhereSql(where, false));
        }
        if (VerifyTools.isNotBlank(odpg.getOrderings())) {
            params.put("orderByCondition", sqlHelper.buildOrderBySql(odpg.getOrderings(), false));
        }

        // return sqlDao.pageForObjects(sqlId, params, odpg, SysUserEntity.class);
        // @formatter:off
        return qdbcBoot.sqlStream()
                .sqlId(sqlId)
                .params(params)
                .pageBy(odpg)
                .resultAs(SysUserEntity.class)
                .list().asPartList();
        // @formatter:on
    }

    /** 批量查询指定角色下的用户信息 (来自sql模板) **/
    public PartList<RoleIdUserResult> getRoleUsersForSql(List<String> roleIds, DbWhere where, OrderPaging odpg) {
        String sqlId = "role.users.query";
        return doQueryRoleUsers(sqlId, roleIds, where, odpg);
    }

    /** 批量查询指定角色下的用户信息 (来自xml模板) **/
    public PartList<RoleIdUserResult> getRoleUsersForXml(List<String> roleIds, DbWhere where, OrderPaging odpg) {
        String sqlId = "SysUserMapper:queryRoleUsers";
        return doQueryRoleUsers(sqlId, roleIds, where, odpg);
    }

    private PartList<RoleIdUserResult> doQueryRoleUsers(String sqlId, List<String> roleIds, DbWhere where,
            OrderPaging odpg) {
        VerifyTools.requireNonNull(roleIds, "roleIds");

        TableJoin tables = TableJoin.of(SysUserRoleEntity.class, "ur", SysUserEntity.class, "u");
        QueryFragmentHelper sqlHelper = qdbcBoot.sqlHelper(tables).fragment();

        Map<String, Object> params = new HashMap<>();
        params.put("selectColumns", sqlHelper.buildSelectFieldsSql("ur.roleId,u.*"));
        params.put("roleIds", roleIds);
        if (VerifyTools.isNotBlank(where)) {
            params.put("whereCondition", sqlHelper.buildWhereSql(where, false));
        }
        if (VerifyTools.isNotBlank(odpg.getOrderings())) {
            params.put("orderByCondition", sqlHelper.buildOrderBySql(odpg.getOrderings(), false));
        }

        // return sqlDao.pageForObjects(sqlId, params, odpg, RoleIdUserResult.class);
        // @formatter:off
        return qdbcBoot.sqlStream()
                .sqlId(sqlId)
                .params(params)
                .pageBy(odpg)
                .resultAs(RoleIdUserResult.class)
                .list().asPartList();
        // @formatter:on
    }

    /** 清除用户角色关联表数据 **/
    public int clearUserRoleRefByTenant(String tenantCode) {
        String sqlId = "user.role.ref.clear.by.tenant";
        Map<String, Object> params = new HashMap<>();
        params.put("tenantCode", tenantCode);
        // return sqlDao.delete(sqlId, params);
        // @formatter:off
        return qdbcBoot.sqlStream()
                .sqlId(sqlId)
                .params(params)
                .delete();
        // @formatter:on
    }

    /** 初始化用户部门数据 **/
    public void initUserDepts(String tenantCode, boolean clear) {
        initUserDepts(tenantCode, tenantCode, clear);
    }

    /** 初始化用户部门数据 **/
    public void initUserDepts(String tenantCode, String prefix, boolean clear) {
        List<String> deptIds = initDepts(tenantCode, prefix, clear);
        initDeptUsers(tenantCode, prefix, deptIds, clear);
    }

    /** 初始化用户角色数据 **/
    public void initUserRoles(String tenantCode, boolean clear) {
        initUserRoles(tenantCode, tenantCode, clear);
    }

    /** 初始化用户角色数据 **/
    public void initUserRoles(String tenantCode, String prefix, boolean clear) {
        List<String> userIds = initUsers(tenantCode, prefix, clear);
        List<String> roleIds = initRoles(tenantCode, prefix, clear);
        initUserRoles(tenantCode, userIds, roleIds, clear);
    }

    private List<String> initDeptUsers(String tenantCode, String prefix, List<String> deptIds, boolean clear) {
        if (clear) { // 清理用户数据
            // @formatter:off
            qdbcBoot.crudStream(SysUserEntity.class)
                .where("tenantCode").equals(tenantCode).end()
                .physicalDelete();
            // @formatter:on
        }
        { // 新增用户数据
            SysUserEntity entity = new SysUserEntity();
            entity.setTenantCode(tenantCode);
            entity.setSuperman(false);
            entity.setGender(Gender.FEMALE);
            entity.setUserType(UserType.USER);
            entity.setUserState(UserState.NORMAL);
            entity.setUserSource(UserSource.INPUT);
            List<SysUserEntity> entities = new ArrayList<>();
            int index = 0;
            for (int i = 0; i < deptIds.size(); i++) {
                String deptId = deptIds.get(i);
                entity.setDeptId(deptId);
                // 第1个部门2个用户, 第2个部门4个, 以此类推
                for (int j = 0; j < (i + 1) * 2; j++) {
                    index++;
                    entity.setId(prefix + "U" + "0000" + StringTools.pad(index, 3));
                    entity.setUserCode(prefix + "U" + StringTools.pad(index, 3));
                    entity.setNickName(prefix + "U" + StringTools.pad(index, 3));
                    entities.add(entity.to(SysUserEntity.class));
                }
            }
            // 批量新增用户
            return qdbcBoot.crudDao(SysUserEntity.class).inserts(entities);
        }
    }

    private List<String> initUsers(String tenantCode, String prefix, boolean clear) {
        if (clear) { // 清理用户数据
            // @formatter:off
            qdbcBoot.crudStream(SysUserEntity.class)
                .where("tenantCode").equals(tenantCode).end()
                .physicalDelete();
            // @formatter:on
        }
        { // 新增用户数据
            SysUserEntity entity = new SysUserEntity();
            entity.setTenantCode(tenantCode);
            entity.setSuperman(false);
            entity.setDeptId("0");
            entity.setGender(Gender.FEMALE);
            entity.setUserType(UserType.USER);
            entity.setUserState(UserState.NORMAL);
            entity.setUserSource(UserSource.INPUT);
            List<SysUserEntity> entities = new ArrayList<>();
            for (int i = 1; i <= 10; i++) {
                entity.setId(prefix + "U" + "0000" + StringTools.pad(i, 3));
                entity.setUserCode(prefix + "U" + StringTools.pad(i, 3));
                entity.setNickName(prefix + "U" + StringTools.pad(i, 3));
                entities.add(entity.to(SysUserEntity.class));
            }
            // 批量新增用户
            // return userDao.inserts(entities);
            return qdbcBoot.crudDao(SysUserEntity.class).inserts(entities);
        }
    }

    private List<String> initRoles(String tenantCode, String prefix, boolean clear) {
        if (clear) { // 清理角色数据
            // @formatter:off
            qdbcBoot.crudStream(SysRoleEntity.class)
                .where("tenantCode").equals(tenantCode).end()
                .physicalDelete();
            // @formatter:on
        }
        { // 新增角色数据
            SysRoleEntity entity = new SysRoleEntity();
            entity.setTenantCode(tenantCode); // 租户编号
            entity.setUserType(UserType.USER); // 用户类型
            entity.setDefaults(false);
            List<SysRoleEntity> entities = new ArrayList<>();
            for (int i = 1; i <= 20; i++) {
                entity.setSortIndex(i);
                entity.setId(prefix + "R" + "0000" + StringTools.pad(i, 3));
                entity.setRoleName(prefix + "R" + StringTools.pad(i, 3));
                entities.add(entity.to(SysRoleEntity.class));
            }
            // 批量新增角色
            // return roleDao.inserts(entities);
            return qdbcBoot.crudDao(SysRoleEntity.class).inserts(entities);
        }
    }

    private List<String> initDepts(String tenantCode, String prefix, boolean clear) {
        if (clear) { // 清理部门数据
            // @formatter:off
            qdbcBoot.crudStream(SysDeptEntity.class)
                .where("tenantCode").equals(tenantCode).end()
                .physicalDelete();
            // @formatter:on
        }
        { // 新增部门数据
            List<SysDeptEntity> entities = new ArrayList<>();
            for (int i = 1; i <= 5; i++) {
                SysDeptEntity entity = new SysDeptEntity();
                entity.setTenantCode(tenantCode);
                entity.setParentCode("0"); // 上级部门编号
                entity.setSortIndex(i); // 排序号(越小越靠前)
                entity.setId(prefix + "D" + "0000" + StringTools.pad(i, 3));
                entity.setDeptCode(prefix + "D" + StringTools.pad(i, 3));
                entity.setDeptName(prefix + "D" + StringTools.pad(i, 3));
                entities.add(entity);
            }
            // 批量新增部门
            // return deptDao.inserts(entities);
            return qdbcBoot.crudDao(SysDeptEntity.class).inserts(entities);
        }
    }

    private List<String> initUserRoles(String tenantCode, List<String> userIds, List<String> roleIds, boolean clear) {
        if (clear) {
            clearUserRoleRefByTenant(tenantCode);
        }

        List<SysUserRoleEntity> entities = new ArrayList<>();
        for (int i = 0; i < userIds.size(); i++) {
            String userId = userIds.get(i);
            for (int j = 0; j < (i + 1) * 2 && j < roleIds.size(); j++) {
                String roleId = roleIds.get(j);
                SysUserRoleEntity entity = new SysUserRoleEntity();
                entity.setUserId(userId);
                entity.setRoleId(roleId);
                entities.add(entity);
            }
        }
        CrudDao<SysUserRoleEntity> dao = qdbcBoot.crudDao(SysUserRoleEntity.class);
        // 批量新增用户角色关系
        return dao.inserts(entities);
    }
}
